package org.budo.mongo.jdbc.driver.parser.jsqlparser;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.budo.jdbc.driver.exception.BudoJdbcDriverNotSupportedException;
import org.budo.jdbc.driver.parser.AbstractSqlStatementParser;
import org.budo.jdbc.driver.statement.JdbcStatement;
import org.budo.jdbc.driver.statement.impl.SET_NAMES;
import org.budo.mongo.jdbc.driver.BudoMongoJdbcResultSetMetaData;
import org.budo.mongo.jdbc.driver.parser.StatementParserUtil;
import org.budo.mongo.jdbc.driver.statement.AbstractMongoStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoAggregateStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoCountStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoDeleteStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoEvalFindStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoFindStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoInsertStatement;
import org.budo.mongo.jdbc.driver.statement.impl.MongoUpdateStatement;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_DATABASES;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_FULL_TABLES;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_TABLES;
import org.budo.mongo.jdbc.driver.statement.impl.SHOW_TABLE_STATUS;
import org.budo.mongo.jdbc.driver.util.JsonUtil;
import org.budo.support.lang.util.MapUtil;
import org.budo.support.lang.util.UuidUtil;

import com.mongodb.QueryOperators;
import com.mongodb.util.JSON;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.BinaryExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.expression.operators.relational.GreaterThanEquals;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.expression.operators.relational.MinorThan;
import net.sf.jsqlparser.expression.operators.relational.MinorThanEquals;
import net.sf.jsqlparser.expression.operators.relational.NotEqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.SetStatement;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.update.Update;

/**
 * @author li
 */
public class JSqlParserMongoStatementParser extends AbstractSqlStatementParser {
    @Override
    public JdbcStatement parse(String sql) throws SQLException {
        String _sql = sql.trim().toUpperCase();
        if (_sql.startsWith("SHOW DATABASES")) {
            return new SHOW_DATABASES();
        }

        if (_sql.startsWith("SHOW TABLE STATUS")) {
            return new SHOW_TABLE_STATUS();
        }

        if (_sql.startsWith("SHOW FULL TABLES")) {
            return new SHOW_FULL_TABLES();
        }

        return super.parse(sql);
    }

    protected JdbcStatement doParseSql(String sql) throws JSQLParserException {
        String _sql = sql.trim().toUpperCase();
        if ("SHOW TABLES".equalsIgnoreCase(_sql)) {
            return new SHOW_TABLES();
        }

        Statement statement = CCJSqlParserUtil.parse(sql);

        if (statement instanceof Insert) {
            return this.mongoInsertStatement(sql, (Insert) statement);
        }

        if (statement instanceof Update) {
            return this.mongoUpdateStatement(sql, (Update) statement);
        }

        if (statement instanceof Delete) {
            return this.mongoDeleteStatement(sql, (Delete) statement);
        }

        if (statement instanceof Select) {
            Select select = (Select) statement;
            SelectBody selectBody = select.getSelectBody();

            if (selectBody instanceof PlainSelect) {
                PlainSelect plainSelect = (PlainSelect) selectBody;

                if (JSqlStatementParserUtil.isCountStatement(plainSelect)) {
                    return this.mongoCountStatement(sql, statement, plainSelect);
                }

                if (JSqlStatementParserUtil.isAggregateStatement(plainSelect)) {
                    return this.mongoAggregateStatement(sql, statement, plainSelect);
                }

                if (JSqlStatementParserUtil.isFindStatement(plainSelect)) {
                    if (JSqlStatementParserUtil.hasSubSelect(plainSelect)) {
                        return this.mongoEvalFindStatement(sql, (Select) statement, plainSelect); // 子查询
                    }

                    return this.mongoFindStatement(sql, (Select) statement, plainSelect);
                }

                throw new BudoJdbcDriverNotSupportedException("not supported sql(plainSelect) : sql=" + sql + ", statement=" + statement + ", type=" + statement.getClass() //
                        + ", selectBody=" + selectBody + ", selectBody.type=" + selectBody.getClass());
            }

            throw new BudoJdbcDriverNotSupportedException("#102 not supported sql(selectBody) : sql=" + sql //
                    + ", statement=" + statement + ", type=" + statement.getClass() //
                    + ", selectBody=" + selectBody + ", selectBody.type=" + selectBody.getClass());
        }

        if (statement instanceof SetStatement) {
            SetStatement setStatement = (SetStatement) statement;
            if (setStatement.getName().equalsIgnoreCase("NAMES")) {
                return new SET_NAMES();
            }
            System.err.println(setStatement.getExpression().getClass());
        }

        throw new BudoJdbcDriverNotSupportedException("#106 not supported sql : " + sql //
                + ", statement=" + statement + ", type=" + statement.getClass());
    }

    /**
     * @see org.budo.mongo.jdbc.driver.parser.jsqlparser.JSqlStatementParserUtil#isAggregateStatement(PlainSelect)
     */
    private JdbcStatement mongoAggregateStatement(String sql, Statement statement, PlainSelect plainSelect) {
        List<Map<String, Object>> aggregatePipeline = new ArrayList<Map<String, Object>>();

        Function selectFunction = JSqlStatementParserUtil.getSelectFunction(plainSelect);
        Column parameterColumn = JSqlStatementParserUtil.getSelectFunctionParameter(plainSelect);
        String functionName = selectFunction.getName();
        String columnName = parameterColumn.getColumnName();

        Map<String, Object> whereDocument = this.whereDocument(plainSelect.getWhere());
        if (null != whereDocument && !whereDocument.isEmpty()) {
            aggregatePipeline.add(MapUtil.stringObjectMap(StatementParserUtil.MATCH, whereDocument));
        }

        Map<String, String> function = MapUtil.stringMap("$" + functionName.toLowerCase(), "$" + columnName);
        Map<String, Object> stringObjectMap = MapUtil.stringObjectMap("_id", functionName, functionName + "(" + columnName + ")", function);
        aggregatePipeline.add(MapUtil.stringObjectMap(StatementParserUtil.GROUP, stringObjectMap));

        Map<String, Object> statementMap = MapUtil.stringObjectMap(AbstractMongoStatement.AGGREGATE_PIPELINE, aggregatePipeline);
        String statementJson = JsonUtil.toJson(statementMap);

        MongoAggregateStatement aggregateStatement = new MongoAggregateStatement();

        String collectionName = JSqlStatementParserUtil.getFromTable(statement).getName();
        aggregateStatement.setCollectionName(collectionName);
        aggregateStatement.setStatementJson(statementJson);

        BudoMongoJdbcResultSetMetaData resultSetMetaData = JSqlStatementParserUtil.aggregateStatementResultSetMetaData(statement);
        aggregateStatement.setResultSetMetaData(resultSetMetaData);

        aggregateStatement.sql(sql);
        return aggregateStatement;
    }

    private JdbcStatement mongoCountStatement(String sql, Statement statement, PlainSelect plainSelect) {
        Map<String, Object> whereDocument = this.whereDocument(plainSelect.getWhere());

        Map<String, Object> statementMap = MapUtil.stringObjectMap(AbstractMongoStatement.WHERE, whereDocument);
        String statementJson = JsonUtil.toJson(statementMap);

        MongoCountStatement countStatement = new MongoCountStatement();
        countStatement.setStatementJson(statementJson);

        String collectionName = JSqlStatementParserUtil.getFromTable(statement).getName();
        countStatement.setCollectionName(collectionName);

        BudoMongoJdbcResultSetMetaData resultSetMetaData = JSqlStatementParserUtil.countStatementResultSetMetaData(statement);
        countStatement.setResultSetMetaData(resultSetMetaData);

        countStatement.sql(sql);
        return countStatement;
    }

    private JdbcStatement mongoDeleteStatement(String sql, Delete delete) {
        String collectionName = delete.getTable().getName();

        Map<String, Object> whereDocument = this.whereDocument(delete.getWhere());

        Map<String, Object> statementMap = MapUtil.stringObjectMap(AbstractMongoStatement.WHERE, whereDocument);
        String statementJson = JsonUtil.toJson(statementMap);

        MongoDeleteStatement deleteStatement = new MongoDeleteStatement();
        deleteStatement.setCollectionName(collectionName);
        deleteStatement.setStatementJson(statementJson);
        deleteStatement.sql(sql);
        return deleteStatement;
    }

    private JdbcStatement mongoUpdateStatement(String sql, Update update) {
        Table table = JSqlStatementParserUtil.getUpdateTable(sql, update);
        String collectionName = table.getName();

        Map<String, Object> valueDocument = JSqlStatementParserUtil.valueDocument(update);
        Map<String, Object> whereDocument = this.whereDocument(update.getWhere());

        Map<String, Object> statementMap = MapUtil.stringObjectMap(AbstractMongoStatement.WHERE, whereDocument, //
                AbstractMongoStatement.UPDATE_VALUES, MapUtil.stringObjectMap(StatementParserUtil.SET, valueDocument));
        String statementJson = JsonUtil.toJson(statementMap);

        MongoUpdateStatement updateStatement = new MongoUpdateStatement();
        updateStatement.setStatementJson(statementJson);
        updateStatement.setCollectionName(collectionName);
        updateStatement.sql(sql);
        return updateStatement;
    }

    private JdbcStatement mongoInsertStatement(String sql, Insert insert) {
        List<Map<String, Object>> insertDocuments = JSqlStatementParserUtil.insertDocuments(insert);

        Map<String, Object> statementDocument = new HashMap<String, Object>();
        statementDocument.put(AbstractMongoStatement.INSERT_VALUES, insertDocuments);
        String statementJson = JsonUtil.toJson(statementDocument);

        MongoInsertStatement insertStatement = new MongoInsertStatement();

        insertStatement.setCollectionName(insert.getTable().getName());
        insertStatement.setStatementJson(statementJson);

        insertStatement.sql(sql);
        return insertStatement;
    }

    private JdbcStatement mongoEvalFindStatement(String sql, Select statement, PlainSelect plainSelect) {
        String script = new String();
        String tmpTableName = null;

        FromItem fromItem = plainSelect.getFromItem();

        while (true) {
            SubSelect subSelect = (SubSelect) fromItem;

            PlainSelect subBody = (PlainSelect) subSelect.getSelectBody();
            FromItem subFrom = subBody.getFromItem();

            if (subFrom instanceof Table) {
                Table table = (Table) subFrom;
                String subTable = table.getName();
                Map<String, Object> subWhere = this.whereDocument(subBody.getWhere());

                Map<String, Object> orderBy = JSqlStatementParserUtil.orderByDocument(subBody.getOrderByElements());
                Map<String, Object> pageDocument = JSqlStatementParserUtil.limitDocument(subBody.getLimit());

                String findQuery = "db['" + subTable + "'].find(" + JSON.serialize(subWhere) + ")";
                if (null != orderBy && !orderBy.isEmpty()) {
                    findQuery += ".sort( " + JSON.serialize(orderBy) + " )";
                }

                if (null != pageDocument) {
                    Object skip = pageDocument.get(AbstractMongoStatement.PAGE_SKIP);
                    if (null != skip && !"".equals(skip)) {
                        findQuery += ".skip( " + skip + " )"; // 这里加空格，AbstractMongoStatement.statementJsonReplaceParameterValues 可能有问题
                    }
                    Object limit = pageDocument.get(AbstractMongoStatement.PAGE_LIMIT);
                    if (null != limit && !"".equals(limit)) {
                        findQuery += ".limit( " + limit + " )";
                    }
                }

                tmpTableName = table.getName() + "_" + UuidUtil.randomUuid(); // 临时表
                script += "db['" + tmpTableName + "'].drop();"; // 删除临时表
                script += "copyToTable(" + findQuery + ", '" + tmpTableName + "');"; // 子查询，将数据存入临时表
                break; // 子查询的from是表是表
            } else if (subFrom instanceof SubSelect) {
                fromItem = subFrom; // 继续循环
            } else {
                throw new RuntimeException("type is " + subFrom.getClass() + ", subFrom=" + subFrom);
            }
        }

        MongoEvalFindStatement mongoEvalFindStatement = new MongoEvalFindStatement();
        Map<String, Object> whereDocument = this.whereDocument(plainSelect.getWhere()); // 最外层的where

        Map<String, Object> orderBy = JSqlStatementParserUtil.orderByDocument(plainSelect.getOrderByElements());
        Map<String, Object> pageDocument = JSqlStatementParserUtil.limitDocument(plainSelect.getLimit());

        String findQuery = "db['" + tmpTableName + "'].find(" + JSON.serialize(whereDocument) + ")";
        if (null != orderBy && !orderBy.isEmpty()) {
            findQuery += ".sort( " + JSON.serialize(orderBy) + " )";
        }

        if (null != pageDocument) {
            Object skip = pageDocument.get(AbstractMongoStatement.PAGE_SKIP);
            if (null != skip && !"".equals(skip)) {
                findQuery += ".skip( " + skip + " )";
            }
            Object limit = pageDocument.get(AbstractMongoStatement.PAGE_LIMIT);
            if (null != limit && !"".equals(limit)) {
                findQuery += ".limit( " + limit + " )";
            }
        }

        script += "return cursorToArray(" + findQuery + ");"; // 从临时表中查询返回数据

        mongoEvalFindStatement.setEvalScript(script);
        return mongoEvalFindStatement;
    }

    private JdbcStatement mongoFindStatement(String sql, Select select, PlainSelect plainSelect) {
        Map<String, Object> statementDocument = new HashMap<String, Object>();

        Map<String, Object> whereDocument = this.whereDocument(plainSelect.getWhere());
        statementDocument.put(AbstractMongoStatement.WHERE, whereDocument);
        statementDocument.put(AbstractMongoStatement.SORT, JSqlStatementParserUtil.orderByDocument(plainSelect.getOrderByElements()));
        statementDocument.put(AbstractMongoStatement.PAGE, JSqlStatementParserUtil.limitDocument(plainSelect.getLimit()));

        String statementJson = JsonUtil.toJson(statementDocument);

        MongoFindStatement findStatement = new MongoFindStatement();
        findStatement.setStatementJson(statementJson);

        Table fromTable = JSqlStatementParserUtil.getFromTable(select);
        String collectionName = this.getTableName(fromTable);
        findStatement.setCollectionName(collectionName);

        BudoMongoJdbcResultSetMetaData resultSetMetaData = JSqlStatementParserUtil.findStatementResultSetMetaData(select);
        findStatement.setResultSetMetaData(resultSetMetaData);

        findStatement.sql(sql);

        return findStatement;
    }

    private String getTableName(Table table) {
        String tableName = table.getName();
        int len = tableName.length();
        if (tableName.charAt(0) == '`' && tableName.charAt(len - 1) == '`') {
            return tableName.substring(1, len - 1);
        }
        return tableName;
    }

    private Map<String, Object> whereDocument(Expression where) {
        if (null == where) {
            return null;
        }

        if (where instanceof ComparisonOperator) { // EqualsTo NotEqualsTo
            ComparisonOperator comparisonOperator = (ComparisonOperator) where;
            Expression leftExpression = comparisonOperator.getLeftExpression();
            Expression rightExpression = comparisonOperator.getRightExpression();

            String fieldName = JSqlStatementParserUtil.fieldName(leftExpression);
            Object value = JSqlStatementParserUtil.parameterValue(rightExpression);

            if (where instanceof EqualsTo) {
                return MapUtil.stringObjectMap(fieldName, value);
            } else if (where instanceof NotEqualsTo) {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.NE, value));
            } else if (where instanceof GreaterThan) {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.GT, value));
            } else if (where instanceof MinorThan) {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.LT, value));
            } else if (where instanceof GreaterThanEquals) {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.GTE, value));
            } else if (where instanceof MinorThanEquals) {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.LTE, value));
            } else {
                throw new BudoJdbcDriverNotSupportedException("where=" + where + ", type=" + where.getClass());
            }
        }

        if (where instanceof LikeExpression) {
            LikeExpression likeExpression = (LikeExpression) where;
            Expression leftExpression = likeExpression.getLeftExpression();
            Expression rightExpression = likeExpression.getRightExpression();

            String fieldName = JSqlStatementParserUtil.fieldName(leftExpression);
            Object value = JSqlStatementParserUtil.parameterValue(rightExpression);

            if (likeExpression.isNot()) {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(QueryOperators.NOT, MapUtil.stringObjectMap(StatementParserUtil.REGEX, value)));
            } else {
                return MapUtil.stringObjectMap(fieldName, MapUtil.stringObjectMap(StatementParserUtil.REGEX, value));
            }
        }

        if (where instanceof Parenthesis) {
            Parenthesis parenthesis = (Parenthesis) where;
            return this.whereDocument(parenthesis.getExpression());
        }

        if (where instanceof InExpression) {
            InExpression inExpression = (InExpression) where;
            Expression leftExpression = inExpression.getLeftExpression();
            Column column = (Column) leftExpression;
            if (leftExpression instanceof Column) {
                column = (Column) leftExpression;
            } else {
                throw new BudoJdbcDriverNotSupportedException("leftExpression=" + leftExpression + ", type=" + leftExpression.getClass());
            }

            List<Object> valueList = new ArrayList<Object>();
            ItemsList itemsList = inExpression.getRightItemsList();
            if (itemsList instanceof ExpressionList) {
                ExpressionList expressionList = (ExpressionList) itemsList;
                List<Expression> expressions = expressionList.getExpressions();
                for (Expression expression : expressions) {
                    Object value = JSqlStatementParserUtil.parameterValue(expression);
                    valueList.add(value);
                }
            } else {
                throw new BudoJdbcDriverNotSupportedException("itemsList=" + itemsList + ", type=" + itemsList.getClass());
            }

            String operator = inExpression.isNot() ? QueryOperators.NIN : QueryOperators.IN;
            return MapUtil.stringObjectMap(column.getColumnName(), //
                    MapUtil.stringObjectMap(operator, valueList));
        }

        if (where instanceof AndExpression || where instanceof OrExpression) { //
            BinaryExpression binaryExpression = (BinaryExpression) where;
            Map<String, Object> left = this.whereDocument(binaryExpression.getLeftExpression());
            Map<String, Object> right = this.whereDocument(binaryExpression.getRightExpression());

            List<Object> valueList = new ArrayList<Object>();
            valueList.add(right);
            valueList.add(left);

            String operator = where instanceof AndExpression ? QueryOperators.AND : QueryOperators.OR;
            return MapUtil.stringObjectMap(operator, valueList);
        }

        // where true

        throw new BudoJdbcDriverNotSupportedException("where=" + where + ", type=" + where.getClass());
    }
}
